CREATE DATABASE CelestialDB
GO
USE CelestialDB
GO
---------------------------------------------------------------------------------------
/* Employees */
CREATE TABLE tblEmployees(

	Username VARCHAR(15) PRIMARY KEY NOT NULL,
	[Password] BINARY(16) NOT NULL,
	UserLevel INT NOT NULL,
	
	EmployeeID INT IDENTITY(1000, 1),
	EmployedOn DATE,
	
	FirstName VARCHAR(30),
	LastName VARCHAR(30),
	Contact VARCHAR(15),
	Email VARCHAR(50),
	
	DOB DATE,
	AddressLine1 VARCHAR(30),
	AddressLine2 VARCHAR(30),
	PostalCode VARCHAR(10),
	
	IsActive BIT
)
GO
---------------------------------------------------------------------------------------
/* Customers */
CREATE TABLE tblCustomers(
	
	CustomerID INT IDENTITY(100000,1) PRIMARY KEY, --Format:1#####
	[Password] BINARY(16) NOT NULL,
	UserLevel INT NOT NULL,
	RegisteredOn DATE,
	
	FirstName VARCHAR(30),
	LastName VARCHAR(30), 
	Email VARCHAR(50),
	Contact VARCHAR(15),
	DOB DATE,
	Country VARCHAR(30),
	City VARCHAR(30),
	[State] VARCHAR(30),
	AddressLine1 VARCHAR(30),
	AddressLine2 VARCHAR(30),
	PostalCode VARCHAR(10),
	
	CreditCardNumber CHAR(16),
	CreditCardType INT,
	CreditCardCVV VARCHAR(3),
	CreditCardExpiry DATE,
	CreditCardHolder VARCHAR(30),
	
	IsActive BIT
)
GO
---------------------------------------------------------------------------------------
/* Product Categories */
CREATE TABLE tblCategories(
	CategoryName VARCHAR(30) PRIMARY KEY,
	ParentCategory VARCHAR(30) FOREIGN KEY REFERENCES tblCategories(CategoryName)
)
---------------------------------------------------------------------------------------
/* Furnitures */
CREATE TABLE tblInventoryItems(
	ItemName VARCHAR(30) PRIMARY KEY,
	Category VARCHAR(30),
	[Description] TEXT,
	[Image] VARCHAR(100),			--file path
	Units INT DEFAULT(0) NOT NULL,
	IsActive BIT DEFAULT(0) NOT NULL
)

/* Products */
CREATE TABLE tblProducts(
	ProductID CHAR(8) PRIMARY KEY,	--Format: [a-z]{3}#####
	ProductName VARCHAR(30) NOT NULL,   
	Category VARCHAR(30),
	[Description] TEXT,
	
	Materials VARCHAR(60),
	WoodFinish VARCHAR(30),
	[Length] DECIMAL,
	Width DECIMAL(18,2),
	Height DECIMAL(18,2),
	Diameter DECIMAL(18,2),
	
	Image1 VARCHAR(100), --path of images
	Image2 VARCHAR(100),
	Image3 VARCHAR(100),
	Image4 VARCHAR(100),
	
	Price DECIMAL(18,2),
	
	IsActive BIT NOT NULL,
	
	FOREIGN KEY(Category) REFERENCES tblCategories(CategoryName)
		ON DELETE SET NULL
)
---------------------------------------------------------------------------------------
CREATE TABLE tblProductInventory(
	ProductID CHAR(8),
	ItemName VARCHAR(30), 
	Quantity INT NOT NULL
	
	PRIMARY KEY(ProductID, ItemName)
	
	FOREIGN KEY(ProductID) REFERENCES tblProducts(ProductID)
		ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(ItemName) REFERENCES tblInventoryItems(ItemName)
		--ON DELETE CASCADE ON UPDATE CASCADE (USE TRIGGER)
)
---------------------------------------------------------------------------------------

/* Customization Groupings */
CREATE TABLE tblCustomizations(
	ParentProduct CHAR(8),	--customsize for...
	ChildProduct CHAR(8),	--customsized by...
	[Group] VARCHAR(30)		--group several options together
	
	PRIMARY KEY(ParentProduct, ChildProduct, [Group])
	
	FOREIGN KEY(ParentProduct) REFERENCES tblProducts(ProductID),
	FOREIGN KEY(ChildProduct) REFERENCES tblProducts(ProductID)
		ON DELETE CASCADE ON UPDATE CASCADE
)
GO
---------------------------------------------------------------------------------------

/* Designs */				/* Unnormalized */
CREATE TABLE tblDesigns(
	CustomerID INT,
	DesignName VARCHAR(20),
	ParentProduct CHAR(8),
	ChildProduct CHAR(8),
	[Group] VARCHAR(30)
	
	PRIMARY KEY(CustomerID, Designname)
	FOREIGN KEY(CustomerID) REFERENCES tblCustomers(CustomerID)
		ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(ParentProduct, ChildProduct, [Group]) REFERENCES tblCustomizations(ParentProduct, ChildProduct, [Group])
		ON UPDATE CASCADE ON DELETE NO ACTION
)
GO
---------------------------------------------------------------------------------------
/* Cart */
CREATE TABLE Cart(
	CustomerID INT, 
	ProductID CHAR(8) NOT NULL,
	Quantity INT NOT NULL,
	UnitPrice DECIMAL(18,2) NOT NULL,
	LastUpdated DATETIME
	
	PRIMARY KEY(CustomerID, ProductID)
	
	FOREIGN KEY(CustomerID) REFERENCES tblCustomers(CustomerID)
		ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(ProductID) REFERENCES tblProducts(ProductID)
		ON DELETE CASCADE ON UPDATE CASCADE
)
GO
---------------------------------------------------------------------------------------
/* Orders */
CREATE TABLE tblOrders(
	OrderID UNIQUEIDENTIFIER PRIMARY KEY,
	CustomerID INT NOT NULL,
	EmployeeID INT,   --The employee who dispatch the order
					  --will be null before dispatch
	TransactionID UNIQUEIDENTIFIER NOT NULL,
	OrderDate DATETIME NOT NULL,
	DeliveryDate DATETIME,
	[Status] INT DEFAULT(0), /* 0 - Pending - delivery date pending accesptance 
								1 - Processed - delivery date request accepted
								2 - Dispatched - sent delivery
								3 - Delivered/Closed - delivery reached
													   signed invoice collected
								9 - Cancelled									*/
	NetAmount DECIMAL(18,2) DEFAULT(0.0) /* Store total amt here even though it is 
										 derivable. Too cumbersone to compute */
										 
	FOREIGN KEY(CustomerID) REFERENCES tblCustomers(CustomerID)
		ON DELETE CASCADE ON UPDATE CASCADE,
)
GO
---------------------------------------------------------------------------------------
/* Order Items */
CREATE TABLE tblOrderItems(
	OrderID UNIQUEIDENTIFIER,
	ProductID CHAR(8),
	Quantity INT NOT NULL,
	UnitPrice DECIMAL(18,2) NOT NULL,
	PriceOverride BIT DEFAULT(0) NOT NULL
	
	PRIMARY KEY(ProductID, OrderID)
	
	FOREIGN KEY(OrderID) REFERENCES tblOrders(OrderID)
		ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY(ProductID) REFERENCES tblProducts(ProductID)
)
GO
---------------------------------------------------------------------------------------
/**************************** TRIGGERS ***********************************************/

/******************************* VIEWS ***********************************************/

/******************************* STORED PROCEDURES ***********************************/
